using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Hotspot : IHotspot
    {
        // Methods
        public IList DropDownListBind()
        {
            string cmdText = "SELECT menu_uid,menu_parent_uid,menu_name FROM sys_menu where menu_parent_uid =1";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    SysMenuInfo info = new SysMenuInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), "");
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getContentHotspotList(int hotspot_id)
        {
            string cmdText = "SELECT a.hotspot_id,a.download_id,a.hotspot_title,b.HOTSPOT_DATE,a.hotspot_link from hotspot_download a inner join hotspot_info b on a.hotspot_id=b.hotspot_id where b.hotspot_id=" + hotspot_id + " and isdel='0' ";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    HotspotInfo info = new HotspotInfo();
                    if (!reader.IsDBNull(0))
                    {
                        info.sHotspot_ID = reader.GetString(0);
                    }
                    if (!reader.IsDBNull(1))
                    {
                        info.Hotspot_DownLoadID = reader.GetInt32(1);
                    }
                    if (!reader.IsDBNull(2))
                    {
                        info.Hotspot_Name = reader.GetString(2);
                    }
                    if (!reader.IsDBNull(3))
                    {
                        info.Hotspot_Date = reader.GetDateTime(3).ToShortDateString();
                    }
                    if (!reader.IsDBNull(4))
                    {
                        info.Hotspot_Link = reader.GetString(4);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public HotspotInfo getHotspotInfo(int hotspot_ID)
        {
            string cmdText = "SELECT hotspot_ID,hotspot_TYPE,hotspot_NAME,hotspot_date FROM hotspot_Info where hotspot_ID=" + hotspot_ID + " and isdel='0'";
            HotspotInfo info = null;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new HotspotInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetDateTime(3).ToShortDateString());
                }
            }
            return info;
        }

        public IList getHotspotList()
        {
            string cmdText = "SELECT Hotspot_ID,Hotspot_TYPE,Hotspot_NAME,HOTSPOT_DATE FROM Hotspot_Info where isdel='0' order by hotspot_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    HotspotInfo info = new HotspotInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetDateTime(3).ToShortDateString());
                    list.Add(info);
                }
            }
            return list;
        }

        public void Insert(HotspotInfo hotspotinfo)
        {
            string cmdText = string.Concat(new object[] { "INSERT INTO Hotspot_info(HOTSPOT_TYPE,HOTSPOT_NAME,HOTSPOT_DATE,isdel) VALUES(", hotspotinfo.Hotspot_Type, ",'", StringUtil.ReplaceStr(hotspotinfo.Hotspot_Name), "',getdate(),'0')" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(HotspotInfo hotsinfo)
        {
            string cmdText = string.Concat(new object[] { "UPDATE hotspot_info SET hotspot_type = '", hotsinfo.Hotspot_Type, "',hotspot_name = '", StringUtil.ReplaceStr(hotsinfo.Hotspot_Name), " ' WHERE hotspot_id =", hotsinfo.Hotspot_ID });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}